-- E Form Edit Deletion Stop Entry
INSERT INTO TableRelationships (TableName,PrimaryKey,ForeignTableName,ForeignKey,ErrorMessage) VALUES ('Invoice_Bank','Inv_BankID','Invoice_GDN','BLID','This Bank / Custom Invoice is used in GDN Invoice / System Vouchers.')

--------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_GDN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_GDN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_GDNitem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_GDNitem]
GO

CREATE TABLE [dbo].[Invoice_GDN] (
	[Inv_GDNID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_GDNDate] [datetime] NOT NULL ,
	[Inv_GDNVNo] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SessionID] [smallint] NOT NULL ,
	[Remarks] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReadOnly] [bit] NOT NULL ,
	[LoginID] [int] NOT NULL ,
	[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDateTime] [datetime] NULL ,
	[ModifyID] [int] NULL ,
	[ModifyHostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifyDateTime] [datetime] NULL ,
	[BLID] [bigint] NULL ,
	[BLNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SumQTY] [numeric](15, 4) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_GDNitem] (
	[Inv_GDNitemID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_GDNID] [bigint] NOT NULL ,
	[Dis_Date] [datetime] NOT NULL ,
	[PartyID] [int] NULL ,
	[ItemID] [smallint] NULL ,
	[VarietyID] [smallint] NULL ,
	[Qty] [numeric](15, 4) NOT NULL ,
	[Type] [tinyint] NOT NULL ,
	[ContainerNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DRemarks] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice_GDN] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_GDN] PRIMARY KEY  CLUSTERED 
	(
		[Inv_GDNID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_GDNitem] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_GDNitem] PRIMARY KEY  CLUSTERED 
	(
		[Inv_GDNitemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_GDN] ADD 
	CONSTRAINT [DF_Invoice_GDN_ReadOnly] DEFAULT (0) FOR [ReadOnly],
	CONSTRAINT [DF_Invoice_GDN_LoginID] DEFAULT (1) FOR [LoginID],
	CONSTRAINT [DF_Invoice_GDN_HostName] DEFAULT (host_name()) FOR [HostName],
	CONSTRAINT [DF_Invoice_GDN_EntryDateTime] DEFAULT (getdate()) FOR [EntryDateTime],
	CONSTRAINT [DF_Invoice_GDN_SumQTY] DEFAULT (0) FOR [SumQTY]
GO

ALTER TABLE [dbo].[Invoice_GDNitem] ADD 
	CONSTRAINT [DF_Invoice_GDNitem_Qty] DEFAULT (0) FOR [Qty],
	CONSTRAINT [DF_Invoice_GDNitem_Type] DEFAULT (0) FOR [Type]
GO